SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO

/*===================================BEGIN=================================*/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCAMPAIGNS_IMPORT_CONTACTS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spCAMPAIGNS_IMPORT_CONTACTS]
GO

CREATE PROC [dbo].[spCAMPAIGNS_IMPORT_CONTACTS]
	@CAMPAIN_ID UNIQUEIDENTIFIER,
	@CONTACTS_LIST VARCHAR(max),
	@OVERWRITE BIT
AS
BEGIN
	IF (LEN(@CONTACTS_LIST) <= 0) RETURN;
	
	IF ((SELECT CHARINDEX(',', @CONTACTS_LIST)) <= 0)
	BEGIN
		SELECT @CONTACTS_LIST = @CONTACTS_LIST + ','
	END
	DECLARE @BeginIndex INT = 0
	DECLARE @SplitIndex INT = (SELECT CHARINDEX(',', @CONTACTS_LIST))
	DECLARE @ProcessedId UNIQUEIDENTIFIER
	DECLARE @ID_TABLE TABLE (CONTACT_ID UNIQUEIDENTIFIER)
	WHILE (@SplitIndex > 0)
	BEGIN
		SELECT @ProcessedId = CONVERT(UNIQUEIDENTIFIER,SUBSTRING(@CONTACTS_LIST, @BeginIndex, @SplitIndex))
		
		IF (@OVERWRITE = 1)
		BEGIN
			DELETE FROM dbo.CAMPAIGNS_CONTACTS 
			WHERE CAMPAIGN_ID = @CAMPAIN_ID
			AND CONTACT_ID = @ProcessedId
		END
		
		INSERT INTO dbo.CAMPAIGNS_CONTACTS( ID , CONTACT_ID ,CAMPAIGN_ID , CREATED_DATE ,FINISHED ,[STATUS])
		VALUES  ( NEWID() ,@ProcessedId ,@CAMPAIN_ID ,GETDATE() ,0 ,0) 
		
		SELECT @BeginIndex = @SplitIndex + 1
		SELECT @SplitIndex = CHARINDEX(',', @CONTACTS_LIST, @SplitIndex + 1)
	END
END

GO


/*===================================END=================================*/


IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'

-- Record the script update
INSERT INTO dbo.[SchemaChanges]
       ([MajorReleaseNumber]
       ,[MinorReleaseNumber]
       ,[BuildReleaseNumber]
       ,[RevisionReleaseNumber]
       ,[ScriptName]
       ,[DateApplied]
       ,[Description])
VALUES
       ('1'
       ,'00'
       ,'01'
       ,'006'
       ,'Upgrade-1.00.01.006.sql'	   
       ,GETDATE()
	   ,'Campaign contacts import proc')
	   COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO        

